package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class SystemConfigurationDAO implements java.io.Serializable{

	Connection con;

	public SystemConfigurationDAO(Connection connection){
		con = connection;
	}

	public HashMap selectSystemConfigurationRecords(String whereClause,String orderByClause)throws SQLException{

		HashMap records = new HashMap();

		String sql ="SELECT CONFIG_NAME,CONFIG_VALUE FROM SYSTEM_CONFIGURATION";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;
		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			records.put(rs.getString("CONFIG_NAME"),rs.getString("CONFIG_VALUE"));
		}
		rs.close();
		stmt.close();
		return records;
	}

	public String getLastInvoiceNumber()throws SQLException{
		String invoiceNumber = null;
		
		String sql = "SELECT CONFIG_VALUE FROM SYSTEM_CONFIGURATION WHERE CONFIG_NAME = 'LAST_INVOICE_NUMBER'";
		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		if(rs.next()){
			invoiceNumber = rs.getString(1);
		}
		rs.close();
		stmt.close();
		
		return invoiceNumber;
	}

	public int saveInvoiceNumber(String invoiceNumber)throws SQLException{
				
		int updateCount = 0;
		// First try an update.
		String sql = "UPDATE SYSTEM_CONFIGURATION SET CONFIG_VALUE = '" + invoiceNumber + "' WHERE CONFIG_NAME = 'LAST_INVOICE_NUMBER'";
		Statement stmt =  con.createStatement();
		updateCount =  stmt.executeUpdate(sql);
		if(updateCount == 0){
			// need to insert the value
			sql = "INSERT INTO SYSTEM_CONFIGURATION(CONFIG_NAME,CONFIG_VALUE) VALUES('LAST_INVOICE_NUMBER','" + invoiceNumber + "')";
			updateCount =  stmt.executeUpdate(sql);
		}
		
		stmt.close();
		
		return updateCount;
	}
	
	
	
	/**

	public SystemConfigurationBean selectSystemConfigurationByPK(SystemConfigurationBean bean)throws SQLException{

		Connection con = getConnection();
		PreparedStatement pstmt = con.prepareStatement("SELECT SYSTEM_CONFIGURATION_ID,CONFIG_NAME,CONFIG_VALUE FROM SYSTEM_CONFIGURATION WHERE SYSTEM_CONFIGURATION_ID = ? ");
		pstmt.setInt(1,bean.getSystemConfigurationId());
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			SystemConfigurationBean bn = new SystemConfigurationBean();
			bn.setSystemConfigurationId(rs.getInt("SYSTEM_CONFIGURATION_ID"));
			bn.setConfigName(rs.getString("CONFIG_NAME"));
			bn.setConfigValue(rs.getString("CONFIG_VALUE"));
			returnConnection(con);
			return bn;
		}else{
			returnConnection(con);
			return null;
		}
	}



	public int addSystemConfiguration(SystemConfigurationBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("INSERT INTO SYSTEM_CONFIGURATION(SYSTEM_CONFIGURATION_ID,CONFIG_NAME,CONFIG_VALUE)VALUES(?,?,?)");
		pstmt.setInt(1,bean.getSystemConfigurationId());
		pstmt.setString(2,bean.getConfigName());
		pstmt.setString(3,bean.getConfigValue());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}

	public int updateSystemConfiguration(SystemConfigurationBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("UPDATE SYSTEM_CONFIGURATION SET CONFIG_NAME = ? ,CONFIG_VALUE = ?  WHERE SYSTEM_CONFIGURATION_ID = ?");
		pstmt.setString(1,bean.getConfigName());
		pstmt.setString(2,bean.getConfigValue());
		pstmt.setInt(3,bean.getSystemConfigurationId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}

	public int deleteSystemConfiguration(SystemConfigurationBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM SYSTEM_CONFIGURATION WHERE SYSTEM_CONFIGURATION_ID = ?");
		pstmt.setInt(1,bean.getSystemConfigurationId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}
	**/
}